package com.turing.post.salesmanage.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.capitalaccounts.util.BankAccount;
import com.turing.post.common.util.PCPSCommonDao;
import com.turing.post.organization.util.IssueCompany;
import com.turing.post.salesmanage.util.PersonalCustom;
import com.turing.post.salesmanage.util.PersonalCustomDao;
import com.turing.post.salesmanage.util.RiskLevelChangeDetail;
import com.turing.post.salesmanage.util.RiskLevelChangeDetailDao;
import com.turing.post.salesmanage.util.SalesManageApplicationException;
import com.turing.post.salesmanage.util.SalesManageRespCode;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * @author zxp
 * @Dec 22, 2012
 */
@Stateless(mappedName = "salesmanage.impl.PersonalCustomDaoImpl")
@Remote
@Local
public class PersonalCustomDaoImpl implements PersonalCustomDao {

	@EJB(mappedName = "	common.impl.PCPSCommonDaoImpl")
	private PCPSCommonDao commonDaoImpl;
	@EJB(mappedName = "	salesmanage.impl.RiskLevelChangeDetailDaoImpl")
	private RiskLevelChangeDetailDao riskLevelChangeDetailDaoImpl;
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	// 根据个人编号查询单条客户信息
	@Override
	public PersonalCustom getPersonalCustomByPersonalId(BigInteger personalId) {
		return _em.find(PersonalCustom.class, personalId);
	}

	// 根据证件号查询个人客户信息，如果证件号重复，记录多条
	@SuppressWarnings("unchecked")
	@Override
	public PersonalCustom getPersonalCustomByIdNo(String idNo)
			throws PCPSApplicationException {
		String jpql = "select * from PersonalCustom p where p.idNo=?1";
		Query query = _em.createNativeQuery(jpql, PersonalCustom.class);
		query.setParameter(1, idNo);
		List<PersonalCustom> list = query.getResultList();
		if (list == null || list.size() == 0) {
			return null;
		} else if (list.size() > 1) {
			throw new SalesManageApplicationException(
					SalesManageApplicationException.ERRCODE_CATEGORY_APPLICATION,
					SalesManageRespCode.PERSONALCUSTOM_ARGS_NULL,
					"个人信息中存在重复的证件号码：" + list.get(0).getIdNo()
							+ "批量导入持卡人实名信息时无法根据证件号获取个人信息编号");
		}
		return list.get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	public PersonalCustom getPersonalCustomByIdTypeAndIdNo(BigInteger idTypeId,
			String idCard) throws PCPSApplicationException {
		String jpql = "select * from PersonalCustom p where idTypeId=?1 and idNo=?2";
		Query query = _em.createNativeQuery(jpql, PersonalCustom.class);
		query.setParameter(1, idTypeId);
		query.setParameter(2, idCard);
		List<PersonalCustom> list = query.getResultList();
		if (list == null || list.size() == 0) {
			return null;
		} else if (list.size() > 1) {
			throw new SalesManageApplicationException(
					SalesManageApplicationException.ERRCODE_CATEGORY_APPLICATION,
					SalesManageRespCode.PERSONALCUSTOM_ARGS_NULL,
					"个人信息中存在重复的证件号码：" + list.get(0).getIdNo()
							+ "批量导入持卡人实名信息时无法根据证件号获取个人信息编号");
		}
		return list.get(0);
	}

	// 根据名称模糊查询当前登录用户所属发卡商下客户,不分页
	// @SuppressWarnings("unchecked")
	// @Override
	// public PersonalCustom getPersonalCustomByName(PCPSWebInput webInput,
	// String name) {
	// String jpql = "";
	// // 平台机构管理员归属机构号为空
	// if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
	// String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
	// jpql = "select * from PersonalCustom p where p.issuecompanyno='"
	// + issueCompanyNo + "' and p.name = '" + name + "'";
	// } else {
	// jpql = "select * from PersonalCustom p where p.name = '" + name
	// + "'";
	// }
	// Query query = _em.createNativeQuery(jpql, PersonalCustom.class);
	// List<PersonalCustom> resultList = query.getResultList();
	// if (resultList.size() < 1) {
	// return null;
	// }
	// return resultList.get(0);
	// }

	// 根据证件类型查询当前发卡商下客户信息,分页,先不分布webservice
	// @SuppressWarnings("unchecked")
	// @Override
	// public List<PersonalCustom> getPersonalCustomByIdTypeId(
	// PCPSWebInput webInput, BigInteger idTypeId) {
	// String jpql = "";
	// // 平台机构管理员归属机构号为空
	// if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
	// String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
	// jpql = "select * from PersonalCustom p where p.issuecompanyno='"
	// + issueCompanyNo + "' and p.idtypeid='" + idTypeId + "'";
	// } else {
	// jpql = "select * from PersonalCustom p where p.idtypeid='"
	// + idTypeId + "'";
	// }
	// Query query = _em.createNativeQuery(jpql, PersonalCustom.class);
	// return query.getResultList();
	// }

	// 分页统计当前发卡商下所有客户信息,分页
	// @SuppressWarnings("unchecked")
	// @Override
	// public List<PersonalCustom> getPersonalCustomList(PCPSWebInput webInput,
	// Integer page, String startTime, String endTime) {
	// int maxResult = Config.PAGE_SIZE;
	// int startPosition = maxResult * page;
	// String sql = "";
	// // 平台机构管理员归属机构号为空
	// if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
	// String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
	// sql = "select * from PersonalCustom t where t.issuecompanyno='"
	// + issueCompanyNo + "'";
	// } else {
	// sql = "select * from PersonalCustom t where 1=1";
	// }
	// // 起始时间
	// if (!PCPSUtil.isEmpty(startTime)) {
	// sql = sql + " and t.createtime >=to_date('" + startTime
	// + "','yyyy-mm-dd')";
	// }
	// // 终止时间
	// if (!PCPSUtil.isEmpty(endTime)) {
	// sql = sql + " and t.createtime<= to_date('" + endTime
	// + "','yyyy-mm-dd')";
	// }
	// sql = sql + " order by t.createtime";
	// Query query = _em.createNativeQuery(sql, PersonalCustom.class)
	// .setFirstResult(startPosition).setMaxResults(maxResult);
	// return query.getResultList();
	// }

	// 分页记录总数
	// @Override
	// public BigInteger getPersonalCustomTotal(PCPSWebInput webInput,
	// String startTime, String endTime) {
	// String sql = "";
	// // 平台机构管理员归属机构号为空
	// if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
	// String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
	// sql = "select nvl(count(*),0) from PersonalCustom t where
	// t.issuecompanyno='"
	// + issueCompanyNo + "'";
	// } else {
	// sql = "select nvl(count(*),0) from PersonalCustom t where 1=1";
	// }
	// // 起始时间
	// if (!PCPSUtil.isEmpty(startTime)) {
	// sql = sql + " and t.createtime >=to_date('" + startTime
	// + "','yyyy-mm-dd')";
	// }
	// // 终止时间
	// if (!PCPSUtil.isEmpty(endTime)) {
	// sql = sql + " and t.createtime<= to_date('" + endTime
	// + "','yyyy-mm-dd')";
	// }
	// Query query = _em.createNativeQuery(sql);
	// return new BigInteger(query.getSingleResult().toString());
	// }

	@Override
	public void insertPersonalCustom(PersonalCustom personalCustom)
			throws PCPSApplicationException {
		// 获取个人客户信息主键
		personalCustom.setPersonalId(commonDaoImpl.getUniqueIntegerKey());
		GeneralDao.insert(_em, personalCustom);
	}

	@Override
	public void insertPersonalCustomNoId(PersonalCustom personalCustom)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, personalCustom);
	}

	@Override
	public void updatePersonalCustom(PersonalCustom personalCustom)
			throws PCPSApplicationException {
		GeneralDao.update(_em, personalCustom);
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public List<PersonalCustom> getPersonalCustomByOperId(
	// PCPSWebInput webInput, String startTime, String endTime,
	// Integer page) {
	// int maxResult = Config.PAGE_SIZE;
	// int startPosition = maxResult * page;
	// String sql = "select * from PersonalCustom t where t.OperId="
	// + webInput.getOperId();
	// // 起始时间
	// if (!PCPSUtil.isEmpty(startTime)) {
	// sql = sql + " and t.createtime >=to_date('" + startTime
	// + "','yyyy-mm-dd')";
	// }
	// // 终止时间
	// if (!PCPSUtil.isEmpty(endTime)) {
	// sql = sql + " and t.createtime<= to_date('" + endTime
	// + "','yyyy-mm-dd')";
	// }
	// sql = sql + " order by t.createtime";
	// Query query = _em.createNativeQuery(sql, PersonalCustom.class)
	// .setFirstResult(startPosition).setMaxResults(maxResult);
	// return query.getResultList();
	// }

	// @Override
	// public BigInteger getPersonalCustomTotalByOperId(PCPSWebInput webInput,
	// String startTime, String endTime) {
	// String sql = "select nvl(count(*),0) from PersonalCustom t where
	// t.OperId="
	// + webInput.getOperId();
	// // 起始时间
	// if (!PCPSUtil.isEmpty(startTime)) {
	// sql = sql + " and t.createtime >=to_date('" + startTime
	// + "','yyyy-mm-dd')";
	// }
	// // 终止时间
	// if (!PCPSUtil.isEmpty(endTime)) {
	// sql = sql + " and t.createtime<= to_date('" + endTime
	// + "','yyyy-mm-dd')";
	// }
	// Query query = _em.createNativeQuery(sql);
	// return new BigInteger(query.getSingleResult().toString());
	// }

	@SuppressWarnings("unchecked")
	@Override
	public List<PersonalCustom> getPersonalCustomListByNameOrIdNo(
			PCPSWebInput webInput, Integer page, String name, String idNo,
			String linkTel) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String branchNo = "";
		// 平台机构管理员归属机构号为空
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			branchNo = PCPSUtil.getIssueCompanyNo(webInput);
		}
		String sql = "select * from PersonalCustom t where (?1 is null or issuecompanyno=?1) and name like ?2 and idNo like ?3 and idNo<>'"
				+ IssueCompany.IDNO + "'";
		if (!PCPSUtil.isEmpty(linkTel)) {
			sql = sql + " and linkTel like '%" + linkTel + "%'";
		}
		sql = sql + " order by t.PersonalId desc";
		Query query = _em.createNativeQuery(sql, PersonalCustom.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, branchNo);

		if (PCPSUtil.isEmpty(name)) {
			name = "";
		}
		query.setParameter(2, "%" + name + "%");
		if (PCPSUtil.isEmpty(idNo)) {
			idNo = "";
		}
		query.setParameter(3, "%" + idNo + "%");
		return query.getResultList();
	}

	@Override
	public BigInteger getPersonalCustomTotalByNameOrIdNo(PCPSWebInput webInput,
			String name, String idNo, String linkTel) {
		String branchNo = "";
		// 平台机构管理员归属机构号为空
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			branchNo = PCPSUtil.getIssueCompanyNo(webInput);
		}
		String sql = "select nvl(count(*),0) from PersonalCustom t where (?1 is null or issuecompanyno=?1) and name like ?2 and idNo like ?3 and idNo<>'"
				+ IssueCompany.IDNO + "'";
		if (!PCPSUtil.isEmpty(linkTel)) {
			sql = sql + " and linkTel like '%" + linkTel + "%'";
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, branchNo);
		if (PCPSUtil.isEmpty(name)) {
			name = "";
		}
		query.setParameter(2, "%" + name + "%");
		if (PCPSUtil.isEmpty(idNo)) {
			idNo = "";
		}
		query.setParameter(3, "%" + idNo + "%");
		return new BigInteger(query.getSingleResult().toString());
	}

	/**
	 * 客户风险等级统计表----报表
	 * 
	 * @param startdatetime
	 *            创建起始时间
	 * @param enddatetime
	 *            创建终止时间
	 * @param riskLevel
	 *            风险等级
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getPersonalCustomByRiskLevel(PCPSWebInput webInput,
			Date startdatetime, Date enddatetime, String riskLevel)
			throws PCPSApplicationException {
		if (webInput == null || startdatetime == null || startdatetime == null
				|| PCPSUtil.isEmpty(riskLevel))
			return null;
		List<Object[]> risklist = new ArrayList<Object[]>();
		Object[] str = null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		List<Object[]> cardlist = getMchAccDetailList(issuecompanyno,
				startdatetime, enddatetime);
		if (cardlist == null || cardlist.size() < 1)
			return null;
		Object[] obj = null;

		for (int i = 0; i < cardlist.size(); i++) {
			obj = cardlist.get(i);

			String cardNo = obj[0] == null ? "" : obj[0].toString();// 卡号
			String mrchName = obj[1] == null ? "" : obj[1].toString();// 商户名称
			if (PCPSUtil.isEmpty(mrchName))
				continue;

			List<Object[]> masteraccnolist = getActivateCardByPersonalId(cardNo
					.trim(), riskLevel);

			if (masteraccnolist == null || masteraccnolist.size() <= 0)
				continue;

			Object[] obj_1 = null;
			for (int i_1 = 0; i_1 < masteraccnolist.size(); i_1++) {
				obj_1 = masteraccnolist.get(i_1);
				if (obj_1 == null)
					continue;
				str = new Object[14];
				str[0] = mrchName;// 商户名称
				str[1] = obj_1[2];// 客户名称
				str[2] = obj_1[3];// 地址
				str[3] = obj_1[4];// 电话
				str[4] = obj_1[5];// 证件名称
				str[5] = obj_1[6];// 证件号
				str[9] = obj_1[7] == null ? ""
						: PCPSUtil
								.getStringYMDByDate(PCPSUtil
										.getDateYMDByString(obj_1[7].toString()
												.trim()));// 建档日期

				List<RiskLevelChangeDetail> listr = riskLevelChangeDetailDaoImpl
						.getRiskLevelChangeDetailListByTyId(issuecompanyno,
								RiskLevelChangeDetail.entityType_person,
								obj_1[8].toString());
				RiskLevelChangeDetail change = null;
				String changedatil = "";
				if (listr != null && listr.size() >= 0) {
					for (int rk = 0; rk < listr.size(); rk++) {
						change = listr.get(rk);
						String OrigRiskLevel = change.getOrigRiskLevel();
						String NewRiskLevel = change.getNewRiskLevel();
						if (changedatil.equals(""))
							changedatil = "[" + getRiskName(OrigRiskLevel)
									+ "----" + getRiskName(NewRiskLevel) + "]";
						else
							changedatil = changedatil + ",["
									+ getRiskName(OrigRiskLevel) + "----"
									+ getRiskName(NewRiskLevel) + "]";
					}
				}
				str[13] = changedatil;
				if (obj_1[1] == null) {
					risklist.add(str);
					continue;
				}

				BankAccount obj_2 = getMasterAccountTranferDetailByMasterAccNo(
						issuecompanyno, obj_1[1].toString().trim());
				if (obj_2 == null) {
					risklist.add(str);
					continue;
				}

				str[6] = obj_2.getAccountName() == null ? "" : obj_2
						.getAccountName();// 账户名称
				str[7] = obj_2.getAccountNo() == null ? "" : obj_2
						.getAccountNo();// 账户号
				str[8] = obj_2.getBankName() == null ? "" : obj_2.getBankName();// 银行名称
				risklist.add(str);
			}
		}
		return risklist;
	}

	// 根据条件检索商户交易记帐明细信息中的卡号列表
	@SuppressWarnings("unchecked")
	private List<Object[]> getMchAccDetailList(String issuecompanyno,
			Date startdatetime, Date enddatetime) {
		if (PCPSUtil.isEmpty(issuecompanyno))
			return null;
		String sql = "select distinct(a.CardNo),b.MerchantName from MchAccDetail a,(select MerchantNo,MerchantName from Merchant where IssueCompanyNo=?1) b where a.MerchantNo = b.merchantno"
				+ " and a.trandate>=?2 and a.trandate<=?3";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startdatetime);
		query.setParameter(3, enddatetime);
		List<Object[]> result = query.getResultList();
		return result;
	}

	// 根据客户id从ActivateCard(激活表里查询主账户列表)
	@SuppressWarnings("unchecked")
	private List<Object[]> getActivateCardByPersonalId(String cardno,
			String riskLevel) {
		if (PCPSUtil.isEmpty(cardno) || PCPSUtil.isEmpty(riskLevel))
			return null;
		String sql = "select distinct(d.cardno),d.masteraccno,c.Name,c.linkaddr,c.LinkTel,c.idtypename,c.IdNo,c.CreateTime,c.PersonalId from ActivateCard d,"
				+ "(select a.PersonalId, a.Name,a.linkaddr, a.LinkTel, b.idtypename, a.IdNo,a.CreateTime from PersonalCustom a, IdType b"
				+ " where a.idtypeid = b.idtypeid and riskLevel=?1) c where ((d.BuyerId = c.PersonalId) or (d.OwnerId = c.PersonalId))"
				+ " and d.cardno = ?2";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, riskLevel);
		query.setParameter(2, cardno);
		List<Object[]> result = query.getResultList();

		return result;
	}

	// 根据主账户号检索主账户转账明细中的银行账户ID
	@SuppressWarnings("unchecked")
	private BankAccount getMasterAccountTranferDetailByMasterAccNo(
			String issuecompanyno, String masterAccNo) {
		if (PCPSUtil.isEmpty(masterAccNo) || PCPSUtil.isEmpty(issuecompanyno))
			return null;
		try {
			String sql = "select AccountName,AccountNo,BankName from BankAccount where BankAccountId in(select distinct(BankAccountId) from MasterAccountTransferDetail where MasterAccNo=?1  and BankAccountId is not null)  and IssueCompanyNo=?2";
			Query query = _em.createNativeQuery(sql, BankAccount.class);
			query.setParameter(1, masterAccNo);
			query.setParameter(2, issuecompanyno);
			return (BankAccount) query.getSingleResult();
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public PersonalCustom getPersonalCustomByUNI(String issueCompanyNO,
			BigInteger idTypeID, String idNO) {
		String sql = "SELECT P.* FROM PERSONALCUSTOM P WHERE P.ISSUECOMPANYNO = ?1 AND P.IDTYPEID = ?2 AND P.IDNO = ?3";
		Query query = _em.createNativeQuery(sql, PersonalCustom.class);
		query.setParameter(1, issueCompanyNO);
		query.setParameter(2, idTypeID);
		query.setParameter(3, idNO);
		if (query.getResultList().size() <= 0) {
			return null;
		}
		return (PersonalCustom) query.getSingleResult();
	}

	private String getRiskName(String riskid) {
		if (riskid.equals("1"))
			return "低风险客户";
		else if (riskid.equals("2"))
			return "中风险客户";
		else if (riskid.equals("3"))
			return "高风险客户";
		else
			return null;
	}

	@Override
	public PersonalCustom getPersonalCustomByIdNOAndIssueCompanyNO(
			String issueCompanyNO, String idNO) {
		String sql = "SELECT P.* FROM PERSONALCUSTOM P WHERE P.ISSUECOMPANYNO = ?1 AND P.IDNO = ?3";
		Query query = _em.createNativeQuery(sql, PersonalCustom.class);
		query.setParameter(1, issueCompanyNO);
		query.setParameter(3, idNO);
		if(query.getResultList().size()<=0){
			return null;
		}
		return (PersonalCustom)query.getSingleResult();
	}
}
